{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT basics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.3\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.0     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introducing the `world` table of countries\n",
    "\n",
    "The example uses a WHERE clause to show the population of 'France'. Note that strings (pieces of text that are data) should be in 'single quotes';\n",
    "\n",
    "**Modify it to show the population of Germany**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>population</th></tr>\n",
       "\t<tr><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>80716000</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " population\\\\\n",
       " <dbl>\\\\\n",
       "\\hline\n",
       "\t 80716000\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| population &lt;dbl&gt; |\n",
       "|---|\n",
       "| 80716000 |\n",
       "\n"
      ],
      "text/plain": [
       "  population\n",
       "1 80716000  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world <- dbReadTable(con, 'world')\n",
    "world %>% filter(name=='Germany') %>% select(population)    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Scandinavia\n",
    "\n",
    "Checking a list The word **IN** allows us to check if an item is in a list. The example shows the name and population for the countries 'Brazil', 'Russia', 'India' and 'China'.\n",
    "\n",
    "**Show the name and the population for 'Sweden', 'Norway' and 'Denmark'.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 3 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>population</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Denmark</td><td>5634437</td></tr>\n",
       "\t<tr><td>Norway </td><td>5124383</td></tr>\n",
       "\t<tr><td>Sweden </td><td>9675885</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 3 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name & population\\\\\n",
       " <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Denmark & 5634437\\\\\n",
       "\t Norway  & 5124383\\\\\n",
       "\t Sweden  & 9675885\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 3 × 2\n",
       "\n",
       "| name &lt;chr&gt; | population &lt;dbl&gt; |\n",
       "|---|---|\n",
       "| Denmark | 5634437 |\n",
       "| Norway  | 5124383 |\n",
       "| Sweden  | 9675885 |\n",
       "\n"
      ],
      "text/plain": [
       "  name    population\n",
       "1 Denmark 5634437   \n",
       "2 Norway  5124383   \n",
       "3 Sweden  9675885   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(name %in% c('Sweden', 'Norway', 'Denmark')) %>%\n",
    "    select(name, population)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Just the right size\n",
    "\n",
    "Which countries are not too small and not too big? `BETWEEN` allows range checking (range specified is inclusive of boundary values). The example below shows countries with an area of 250,000-300,000 sq. km. Modify it to show the country and the area for countries with an area between 200,000 and 250,000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 8 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>area</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Belarus       </td><td>207600</td></tr>\n",
       "\t<tr><td>Ghana         </td><td>238533</td></tr>\n",
       "\t<tr><td>Guinea        </td><td>245857</td></tr>\n",
       "\t<tr><td>Guyana        </td><td>214969</td></tr>\n",
       "\t<tr><td>Laos          </td><td>236800</td></tr>\n",
       "\t<tr><td>Romania       </td><td>238391</td></tr>\n",
       "\t<tr><td>Uganda        </td><td>241550</td></tr>\n",
       "\t<tr><td>United Kingdom</td><td>242900</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 8 × 2\n",
       "\\begin{tabular}{ll}\n",
       " name & area\\\\\n",
       " <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Belarus        & 207600\\\\\n",
       "\t Ghana          & 238533\\\\\n",
       "\t Guinea         & 245857\\\\\n",
       "\t Guyana         & 214969\\\\\n",
       "\t Laos           & 236800\\\\\n",
       "\t Romania        & 238391\\\\\n",
       "\t Uganda         & 241550\\\\\n",
       "\t United Kingdom & 242900\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 8 × 2\n",
       "\n",
       "| name &lt;chr&gt; | area &lt;dbl&gt; |\n",
       "|---|---|\n",
       "| Belarus        | 207600 |\n",
       "| Ghana          | 238533 |\n",
       "| Guinea         | 245857 |\n",
       "| Guyana         | 214969 |\n",
       "| Laos           | 236800 |\n",
       "| Romania        | 238391 |\n",
       "| Uganda         | 241550 |\n",
       "| United Kingdom | 242900 |\n",
       "\n"
      ],
      "text/plain": [
       "  name           area  \n",
       "1 Belarus        207600\n",
       "2 Ghana          238533\n",
       "3 Guinea         245857\n",
       "4 Guyana         214969\n",
       "5 Laos           236800\n",
       "6 Romania        238391\n",
       "7 Uganda         241550\n",
       "8 United Kingdom 242900"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "world %>% \n",
    "    filter(between(area, 200000, 250000)) %>% \n",
    "    select(name, area)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
